﻿using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

class DB
{
    //public static string strConnection = ConfigurationManager.AppSettings["connectionQLHS"].ToString();
    private string strConnection;
    private SqlCommand cmd;
    public bool isOpen;
    // ham khoi tao

    public DB()
    {
        string servername, database, username, password;
        servername = "TTTHLT";
        database = "HongPhucGoldDB";
        username = "sa";
        password = "123456";
        strConnection = "server=.;Data Source=" + servername + ";Initial Catalog=" + database + ";User ID=" + username + ";Password='" + password + "'";
        SqlConnection conn = new SqlConnection(strConnection);
        this.cmd = conn.CreateCommand();
    }
    // Mo ket noi
    public void Open()
    {
        if (isOpen) return;
        try
        {
            if (cmd.Connection.State != ConnectionState.Open)
            {
                cmd.Connection.Open();
                isOpen = true;
            }
        }
        catch
        {
            isOpen = false;
        }
    }
    // Dong ket noi
    public void Close()
    {
        cmd.Connection.Close();
        isOpen = false;
    }
    // Giai phong tai nguyen
    public void Dispose()
    {
        cmd.Connection.Dispose();
        cmd.Dispose();
        this.Close();
    }
    //Thi hanh lenh voi cau SQL 
    public bool ExecuteNonQuery(string commandText)
    {
        bool bResult = false;
        try
        {
            this.Open();
            cmd.CommandText = commandText;
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
            bResult = true;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            cmd.Dispose();
        }
        return bResult;
    }

    //Thi hanh lenh voi cau SQL co kieu
    public bool ExecuteNonQuery(string commandText, CommandType type)
    {
        bool bResult = false;
        try
        {
            this.Open();
            cmd.CommandText = commandText;
            cmd.CommandType = type;
            cmd.ExecuteNonQuery();
            bResult = true;
        }
        catch (Exception ex)
        {
            throw new Exception("Không thể thi hành lệnh này : " + ex.ToString());
        }
        finally
        {
            cmd.Dispose();
        }
        return bResult;
    }

    public DataTable CreateDatatable(string commandText)
    {
        DataTable dt = new DataTable();
        SqlDataAdapter dap = new SqlDataAdapter();
        try
        {
            cmd.CommandText = commandText;
            cmd.CommandType = CommandType.Text;
            dap.SelectCommand = cmd;
            dap.Fill(dt);
        }
        catch (Exception ex)
        {
            throw new Exception("Không thể tạo DataTable : " + ex.ToString());
        }
        finally
        {
            cmd.Dispose();
        }
        return dt;
    }
}

